Imports System.Data.OleDb
Imports DTO

Public Class KhachHangDAO

    Inherits AbstractDAO

    Public Function Lay_Bang(ByVal chuoi_lenh As String) As DataTable
        Dim dt As New DataTable
        Dim chuoi_ket_noi As New OleDbConnection
        chuoi_ket_noi = AbstractDAO.ConnectionData()
        Dim bo_thich_ung As OleDbDataAdapter
        bo_thich_ung = New OleDbDataAdapter(chuoi_lenh, chuoi_ket_noi)
        bo_thich_ung.FillSchema(dt, SchemaType.Mapped)
        bo_thich_ung.Fill(dt)
        Return dt
    End Function

    Public Function DocCauTruc(ByVal chuoi_lenh As String) As DataTable
        Dim dt As New DataTable
        Dim chuoi_ket_noi As OleDbConnection
        chuoi_ket_noi = AbstractDAO.ConnectionData()
        Dim bo_thich_ung As OleDbDataAdapter
        bo_thich_ung = New OleDbDataAdapter(chuoi_lenh, chuoi_ket_noi)
        bo_thich_ung.FillSchema(dt, SchemaType.Mapped)
        Return dt
    End Function

    Public Function LayBangKhachHang() As DataTable
        Dim dt As New DataTable
        Dim cn As OleDbConnection
        cn = AbstractDAO.ConnectionData()
        Dim strSQL As String
        strSQL = "select kh.MaKhachHang, kh.TenKH, kh.PassPort, kh.CMND, kh.DiaChi, lkh.TenLoaiKH, lp.TenLoaiPhong from KhachHang kh, LoaiKhachHang lkh, LoaiPhong lp where kh.MaLoaiKH = lkh.MaLoaiKH and kh.MaLoaiPhong = lp.MaLoaiPhong group by kh.MaKhachHang, kh.TenKH, kh.PassPort, kh.CMND, kh.DiaChi, lkh.TenLoaiKH, lp.TenLoaiPhong"
        Dim da As OleDbDataAdapter
        da = New OleDbDataAdapter(strSQL, cn)
        da.Fill(dt)
        cn.Close()
        Return dt
    End Function

    Public Function TinKiemTheoNhieuTieuChi(ByVal khCRT As KhachHangCRT) As DataTable
        Dim dt As New DataTable
        Dim cn As New OleDbConnection
        cn = AbstractDAO.ConnectionData()
        Dim strSQL As String
        strSQL = "select kh.MakhachHang, kh.TenKH, kh.MaLoaiKH, kh.PassPort, kh.CMND, kh.DiaChi, kh.MaLoaiPhong from KhachHang kh, LoaiKhachHang lkh, LoaiPhong lp where kh.MaLoaiKH = lkh.MaLoaiKH and kh.MaLoaiPhong = lp.MaLoaiPhong"
        Dim cmb As New OleDbCommand()
        Dim strTen_KH As String = "1=1"
        Dim strLoai_KH As String = "2=2"
        Dim strLoai_Phong As String = "3=3"
        If khCRT.CHK_TenKH = True Then
            strTen_KH = "kh.TenKH like ?"
            cmb.Parameters.Add("@kh.TenKH", OleDbType.WChar)
            cmb.Parameters("@kh.TenKH").Value = "%" + khCRT.TenKH.ToString() + "%"
        End If

        If khCRT.CHK_LoaiKH = True Then
            strLoai_KH = "lkh.MaLoaiKH = ?"
            cmb.Parameters.Add("@lkh.MaLoaiKH", OleDbType.Integer)
            cmb.Parameters("@lkh.MaLoaiKH").Value = khCRT.LoaiKH
        End If

        If khCRT.CHK_LoaiPhong = True Then
            strLoai_Phong = "lp.MaLoaiPhong = ?"
            cmb.Parameters.Add("@lp.MaLoaiPhong", OleDbType.Integer)
            cmb.Parameters("@lp.MaLoaiPhong").Value = khCRT.LoaiPhong
        End If
        strSQL += " and " + strTen_KH + " and " + strLoai_KH + " and " + strLoai_Phong
        cmb.Connection = cn
        cmb.CommandText = strSQL
        Dim da As New OleDbDataAdapter(cmb)
        da.Fill(dt)
        Return dt
    End Function

    Public Sub ThemKhachHang(ByVal dr As DataRow)
        Dim cn As OleDbConnection
        cn = AbstractDAO.ConnectionData()
        Dim strSQL As String
        strSQL = "insert into KhachHang(TenKH, MaLoaiKH, PassPort, CMND, DiaChi, MaLoaiPhong) values(?,?,?,?,?,?)"
        Dim cmb As New OleDbCommand(strSQL, cn)

        cmb.Parameters.Add("@TenKH", OleDbType.WChar)
        cmb.Parameters.Add("@MaLoaiKH", OleDbType.Integer)
        cmb.Parameters.Add("@PassPort", OleDbType.Integer)
        cmb.Parameters.Add("@CMND", OleDbType.Integer)
        cmb.Parameters.Add("@DiaChi", OleDbType.WChar)
        cmb.Parameters.Add("@MaLoaiPhong", OleDbType.Integer)

        cmb.Parameters("@TenKH").Value = dr("TenKH")
        cmb.Parameters("@MaLoaiKH").Value = dr("MaLoaiKH")
        cmb.Parameters("@PassPort").Value = dr("PassPort")
        cmb.Parameters("@CMND").Value = dr("CMND")
        cmb.Parameters("@DiaChi").Value = dr("DiaChi")
        cmb.Parameters("@MaLoaiPhong").Value = dr("MaLoaiPhong")

        cmb.ExecuteNonQuery()

        cn.Close()
    End Sub

    Public Sub CapNhatKhachHang(ByVal dr As DataRow)
        Dim cn As OleDbConnection
        cn = AbstractDAO.ConnectionData()
        Dim strSQL As String
        strSQL = "update KhachHang " & _
        "set TenKH = ?, MaLoaiKH = ?, PassPort = ?, CMND = ?, DiaChi = ?, MaLoaiPhong = ?" & _
        "where MaKhachHang = ? "
        Dim cmb As New OleDbCommand(strSQL, cn)

        cmb.Parameters.Add("@TenKH", OleDbType.WChar)
        cmb.Parameters.Add("@MaLoaiKH", OleDbType.Integer)
        cmb.Parameters.Add("@PassPort", OleDbType.Integer)
        cmb.Parameters.Add("@CMND", OleDbType.Integer)
        cmb.Parameters.Add("@DiaChi", OleDbType.WChar)
        cmb.Parameters.Add("@MaLoaiPhong", OleDbType.Integer)
        cmb.Parameters.Add("@MaKhachHang", OleDbType.Integer)

        cmb.Parameters("@TenKH").Value = dr("TenKH")
        cmb.Parameters("@MaLoaiKH").Value = dr("MaLoaiKH")
        cmb.Parameters("@PassPort").Value = dr("PassPort")
        cmb.Parameters("@CMND").Value = dr("CMND")
        cmb.Parameters("@DiaChi").Value = dr("DiaChi")
        cmb.Parameters("@MaLoaiPhong").Value = dr("MaLoaiPhong")
        cmb.Parameters("@MaKhachHang").Value = dr("MaKhachHang")

        cmb.ExecuteNonQuery()
        cn.Close()
    End Sub

    Public Sub XoaKhachHang(ByVal dr As DataRow)
        Dim cn As OleDbConnection
        cn = AbstractDAO.ConnectionData()
        Dim strSQL As String
        strSQL = "delete from KhachHang where MaKhachHang = ? "
        Dim cmb As New OleDbCommand(strSQL, cn)
        cmb.Parameters.Add("@MaKhachHang", OleDbType.Integer)
        cmb.Parameters("@MaKhachHang").Value = dr("MaKhachHang")
        cmb.ExecuteNonQuery()
        cn.Close()
    End Sub

End Class
